Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Which Columns Should Be Indexed?

Use the following guidelines to decide which columns to index:

  Choose columns frequently specified in WHERE clauses. Frequently accessed columns can most benefit from indexes.
  Don’t index columns that do not have many unique values. Columns in which a good percentage of rows are duplicates cannot take advantage of indexing.
  Columns that have unique values are excellent candidates for indexing. Oracle automatically indexes columns that are unique or that are primary keys defined with constraints. These columns are most effectively optimized by indexes.
  Index columns that are foreign keys of referential integrity constraints in cases where large numbers of concurrent INSERT, UPDATE, and DELETE statements access both the parent and child tables. Such an index allows the child table to be updated without having to lock the parent table.
  Columns that are commonly used to join tables are good candidates for indexing.
  Frequently modified columns probably should not be index columns because of the overhead involved in updating the index.


NOTE:  Remember that some penalty is associated with performing INSERT, UPDATE, and DELETE statements on columns that are indexed. If you have a high number of those statements, an index may hurt more than help. Use SQL Trace on the SQL statements that access that table both with and without an index. Compare the results.

Composite Indexes

Composite indexes may be more effective than individual indexes in situations such as the following:

  When two columns are not unique individually but are unique together, composite indexes may work very well. For example, columns A and B have few unique values, but rows with a particular A AND B are mostly unique. Look for WHERE clauses with AND operators.
  If all values of a SELECT statement are in a composite index, the table is not queried; the result is returned from the index.
  If several different queries select the same rows by using different WHERE clauses based on different columns, consider creating a composite index with all those columns used in the WHERE statements.

If carefully designed, composite indexes can be quite useful. As with single-column indexes, they are most effective if applications are written with the indexes in mind.

Once you create the index, periodically use SQL Trace to determine whether your queries are taking advantage of the index. It may be worth the effort to try the query with and without indexes and compare the results to see whether the index is worth the space it is using.

How To Avoid an Index

If you have an application that can take advantage of an index, but contains a few SQL statements that result in poor performance when they use an index, you can tell the optimizer to bypass the index. There are several ways this can be done:

  Write the SQL statement to avoid using a SELECT statement on an indexed column. By not selecting the column or set of columns that are indexed, you avoid the index.
  Use hints. When you use hints in your SQL statements, you can tell the optimizer not to use the index for this particular SQL statement. Hints are detailed in Chapter 30, “Using Hints.”

In this manner, you can design your database to effectively use indexes and have the flexibility to avoid the index when it is not optimal to do so.

Review of Indexes

If used properly, indexes can significantly improve performance in your system. You must first decide whether an index is appropriate for the data and access patterns in your particular system. Having decided to use an index, you must decide which columns to index.

Indexing an inappropriate column or table can actually reduce performance. Indexing appropriately can greatly improve performance by reducing I/O and speeding access times. If necessary, use indexes and take advantage of hints to avoid the index when it would be inefficient to use it.

Careful planning and periodic testing with SQL Trace can lead to a very effective use of indexes, with optimal performance as the outcome.

Clusters

A cluster, sometimes called an index cluster, is an optional method of storing tables in an Oracle database. In a cluster, multiple related tables are stored to more efficiently improve access time to the related items. Clusters provide the most benefit when the related data is frequently accessed together. The existence of a cluster is transparent to users and to applications; the cluster only affects how data is stored.

Clusters can be advantageous in certain situations and disadvantageous in others. You must be careful in determining whether a cluster can help performance in your configuration. Typically, clusters are advantageous if the clustered, related data is used primarily in joins because the data to be used in the join is retrieved together in one I/O operation.

If you have two tables with related data that are frequently accessed together, having a cluster can improve performance by preloading the related data into the SGA. Because you frequently use the data together, having that data already in the SGA greatly reduces access time.

If you do not typically use the information together, you will find no performance benefit from using a cluster. There is even a slight disadvantage because the SGA space is taken up by the additional table information.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.